package com.pro.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;

import com.pro.entity.ProductEntity;
import com.pro.util.DBManager;
import com.pro.util.QueryResult;

public class ProductDaoImpl {
	//pid 100 显示在首页  0 不显示 其他 根据产品分类id查询
	public ArrayList<ProductEntity> productAll(int pid){
		ArrayList<ProductEntity> alist= new ArrayList<ProductEntity>();
		String sqlString=null;
		if(pid==100) {
	     sqlString="select gid,create_time,productname,img,texture,price,price_rmb,pid,show_home from product"
	    		+ " where show_home = 1";
		}else if(pid==0) {
			sqlString="select gid,create_time,productname,img,texture,price,price_rmb,pid,show_home from product"
		    		+ " where show_home = 0";
		}else {
			sqlString="select gid,create_time,productname,img,texture,price,price_rmb,pid,show_home from product"
		    		+ " where pid = '"+pid+"'";
		}
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  alist.add(new ProductEntity(rSet.rs.getInt("gid"),
	            		rSet.rs.getString("create_time"),
	            		rSet.rs.getString("productname"),
	            		rSet.rs.getString("img"),
	            		rSet.rs.getString("texture"),
	            		rSet.rs.getString("price"),
	            		rSet.rs.getString("price_rmb"),
	            		rSet.rs.getInt("pid"),
	            		rSet.rs.getInt("show_home")));
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return alist;
	}
	//实现分页 并和产品分类表连接 并且根据条件进行查询
	public ArrayList<ProductEntity> productAdminList(Integer page,Integer limit,String pid,
			String showHome, String beginDate, String endDate){
		ArrayList<ProductEntity> alist= new ArrayList<ProductEntity>();
	    String sqlString="select p.gid,p.create_time,p.productname,p.img,p.texture,p.price,p.price_rmb,p.pid,p.show_home,pt.name from product p,product_type pt " + 
	    		"where p.pid = pt.id";
	    if(pid!=null&&!pid.equals("")) {
	    	sqlString = sqlString + " and p.pid = '"+pid+"'";
	    }
	    if(showHome!=null&&!showHome.equals("")) {
	    	sqlString = sqlString + " and p.show_home = '"+showHome+"'";
	    }
	    if(beginDate!=null&&!beginDate.equals("")) {
	    	sqlString = sqlString + " and p.create_time >= '"+beginDate+"'";
	    }
	    if(endDate!=null&&!endDate.equals("")){
	    	sqlString = sqlString + " and p.create_time <= '"+endDate+"'";
	    }
	    sqlString = sqlString+" order by p.create_time desc";
	    if(page!=null&&limit!=null) {
	    	sqlString+= " limit "+(page-1)*limit+","+limit;
	    }
		System.out.println(sqlString);
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  alist.add(new ProductEntity(rSet.rs.getInt("gid"),
		            		rSet.rs.getString("create_time"),
		            		rSet.rs.getString("productname"),
		            		rSet.rs.getString("img"),
		            		rSet.rs.getString("texture"),
		            		rSet.rs.getString("price"),
		            		rSet.rs.getString("price_rmb"),
		            		rSet.rs.getInt("pid"),
		            		rSet.rs.getInt("show_home"),
		            		rSet.rs.getString("name")));
	            		
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return alist;
	}
	//根据产品id进行查询
	public ProductEntity productDetail(int gid){
		ProductEntity pEntity= null;
		String sqlString="select gid,create_time,productname,img,texture,price,price_rmb,pid,show_home from product"
		    		+ " where gid = '"+gid+"'";
	    QueryResult rSet=DBManager.executeQuery(sqlString);
	      if (rSet!=null) {
	        try {
	          while (rSet.rs.next()) {
	        	  pEntity = new ProductEntity(rSet.rs.getInt("gid"),
	            		rSet.rs.getString("create_time"),
	            		rSet.rs.getString("productname"),
	            		rSet.rs.getString("img"),
	            		rSet.rs.getString("texture"),
	            		rSet.rs.getString("price"),
	            		rSet.rs.getString("price_rmb"),
	            		rSet.rs.getInt("pid"),
	            		rSet.rs.getInt("show_home"));
	          }
	        } catch (SQLException e) {
	          e.printStackTrace();
	        }
	      }
	      rSet.close();
	    return pEntity;
	}
	
	public int productUpdate(ProductEntity pEntity) {
 		String sqlString="update product set create_time='"+pEntity.getCreateTime()+"',productname='"+pEntity.getName()+"',img='"+pEntity.getImg()+"',texture='"+pEntity.getTexture()+"',price='"+pEntity.getPrice()+"',price_rmb='"+pEntity.getPriceRmb()+"',pid='"+pEntity.getPid()+"',show_home='"+pEntity.getShowHome()+"' where gid = '"+pEntity.getId()+"'";
 		 int result= DBManager.executeUpdate(sqlString);
 		return result;
 	}
	
	 public int productAdd(ProductEntity pEntity) {
 		String sqlString="insert into product(create_time,productname,img,texture,price,price_rmb,pid,show_home) values('"+pEntity.getCreateTime()+"','"+pEntity.getName()+"','"+pEntity.getImg()+"','"+pEntity.getTexture()+"','"+pEntity.getPrice()+"','"+pEntity.getPriceRmb()+"','"+pEntity.getPid()+"','"+pEntity.getShowHome()+"')";
 		 int result= DBManager.executeUpdate(sqlString);
 		return result;
 	}
	 
	 public int productDelete(int id) {
			// TODO Auto-generated method stub
			String sqlString = "delete from product where gid = '"+id+"'";
			int result = DBManager.executeUpdate(sqlString);
			return result;
		}
}
